<?php

	namespace App\Http\Controllers\Admin\ExportContract;

	use Illuminate\Http\Request;
	use App\Http\Controllers\Controller;
	use DB;
	use PhpOffice\PhpSpreadsheet\Cell\DataType;
	use PhpOffice\PhpSpreadsheet\IOFactory;
	use QrCode;

	class ExportContractPublicController extends Controller
	{

		private function getvolclass($val)
		{
			if ($val->class === '/') {
				# 电压等级
				$voltage_class = "{$val->voltage}";
			}
			else if ($val->voltage === '/') {
				# 分类
				$voltage_class = "{$val->class}";
			}
			else {
				# 电压等级+分类
				$voltage_class = "{$val->voltage}/{$val->class}";
			}
			return $voltage_class;
		}

		public function export(Request $request)
		{
			$isLan = config('constant.is_lan');
			// sleep (5);
			$contract_id = $request->id;
			$data_gaiyao = DB::table('a_contract_gaiyao')->where('contract_id', $contract_id)->orderBy('id', 'asc')->get();
			if ($data_gaiyao->isEmpty()) {
				return '内容为空';
			}
			$data_contract = DB::table('a_contract')->find($contract_id);
			// dd($data_contract);

			// dd(base_path());
			// dd(public_path());
			/*if($isLan){
				$template_file = './storage/ExcelTemplate/lan/检验合同.xls';
			}else{
				$template_file = './storage/ExcelTemplate/检验合同.xls'; //考虑到兼容Linux，斜杠用“/”
			}*/
            $template_file = './storage/ExcelTemplate/检验合同.xls'; //考虑到兼容Linux，斜杠用“/”
			if (!file_exists($template_file)) {
				return '模板文件不存在';
			}
			$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($template_file); //读取模板文件
			// $worksheet = $spreadsheet->getActiveSheet();//获取到激活的表单
			$worksheet = $spreadsheet->getSheet(0);                                   //按索引获取表单，从 0 开始
			// $worksheet2->setTitle('Copy');
			// $worksheet = $spreadsheet->getSheetByName('Sheet1');

			// Worksheet addExternalSheet(Worksheet $pSheet, nu l l|int $iSheetIndex = null)  //添加外部工作表
			// $worksheet->setCellValue('A4', 'Hello World !');//写入单元格方式 2
			// $columnIndex = 5;
			// $rowIndex = 15;
			// $worksheet->setCellValueByColumnAndRow($columnIndex, $rowIndex, '我是A5,5');//写入单元格方式 3
			// $worksheet->insertNewRowBefore(7, 2);//以下代码在第7行之前插入2个新行
			// $worksheet->getRowDimension('10')->setRowHeight(15);//设置行高
			// $worksheet->mergeCells('A18:E22');//合并单元格
			if (!$isLan) {
				/* ---------------- 二维码 -------------------- */
				$domain       = $request->server('HTTP_HOST');                                                //域名
				$contract_key = DB::table('a_status_time')->where('contract_id', $contract_id)->value('key'); //批次的密钥
				$url_name     = 'http://' . $domain . "/viewStatus?id={$contract_id}&k={$contract_key}";
				$qr_code_path = "./QrCodeImage/QrCode{$contract_id}.png";

				$qrCode = QrCode::format('png');
				$qrCode = $qrCode->size(150);
				$qrCode = $qrCode->margin(0);
				$qrCode->generate($url_name, $qr_code_path);

				$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
				// $drawing->setName('Logo');
				// $drawing->setDescription('Logo');
				$drawing->setPath($qr_code_path);                                                             //图片的路径
				$drawing->setResizeProportional(true);                                                        //true 是保持宽高比
				$drawing->setHeight(93);                                                                      //高度
				$drawing->setOffsetX(5);
				$drawing->setCoordinates('M4');//指定位置

				// 要将上述图形添加到工作表，请使用以下代码段。PhpSpreadsheet创建绘图和工作表之间的链接：
				$drawing->setWorksheet($worksheet);
				/* ---------------- 二维码 -------------------- */
			}

			$sample_chuli = $data_contract->sample_chuli;//不合格样品处理

			$get_report = $data_contract->get_report;//取报告方式

			$setting = DB::table('setting')->find(1);
            $worksheet->mergeCells('A1:N1')->getStyle('A1')->getAlignment()->setHorizontal('center');
            $worksheet->getCell('A1')->setValue($setting->name_rep);                //报告名称
            $worksheet->getCell('K3')->setValue($data_contract->batchNum);          //合同编号
			$worksheet->getCell('B4')->setValue($data_contract->entrusted_company); //委托单位
			$worksheet->getCell('K4')->setValue($data_contract->test_kind);         //检测性质
			$worksheet->getCell('B5')->setValue($data_contract->client_addr);       //单位地址
			$worksheet->getCell('K5')->setValue($data_contract->client_contact);    //联系人
			$worksheet->getCell('B6')->setValue($data_contract->client_name);       //送检单位
			$worksheet->getCell('K6')->setValue($data_contract->client_tel);        //联系电话
			$worksheet->getCell('C11')->setValue($sample_chuli);                    //不合格样品处理
			$worksheet->getCell('F11')->setValue($get_report);                      //取报告方式
			$worksheet->getCell('J11')->setValue($data_contract->deadline);         //要求完成日期
			//将重复数据合并
			$tempdata = [];
			foreach ($data_gaiyao as $k => $val) {
				$voltage_class = $this->getvolclass($val);
				$k             = $val->tool_id . $voltage_class . $val->unit . $val->item_names . $val->std_id;
				isset($tempdata[$k]) ? ($tempdata[$k]->count += $val->count) : ($tempdata[$k] = $val);
			}
			$data_gaiyao = $tempdata;
			$first_flag  = false; //第一次循环的标志
			$rowIndex    = 10;
			$totalCount  = 0;
			foreach ($data_gaiyao as $val) {
				if ($first_flag) {
					# code...
					$worksheet->insertNewRowBefore($rowIndex, 1);               //在第10行之前插入1个新行
					// $worksheet->getRowDimension('10')->setRowHeight(15);//设置行高
					// $worksheet->mergeCells('A18:E22');//合并单元格
					$worksheet->mergeCells('A' . $rowIndex . ':C' . $rowIndex); //合并单元格--样品名称
					$worksheet->mergeCells('D' . $rowIndex . ':E' . $rowIndex); //合并单元格--电压等级+分类
					$worksheet->mergeCells('G' . $rowIndex . ':L' . $rowIndex); //合并单元格--检验项目
					$worksheet->mergeCells('M' . $rowIndex . ':N' . $rowIndex); //合并单元格--检验标准
				}
				$voltage_class = $this->getvolclass($val);

				$worksheet->setCellValueByColumnAndRow(1, $rowIndex, $val->tool_name);          //样品名称
				$worksheet->setCellValueByColumnAndRow(4, $rowIndex, $voltage_class);           //电压等级+分类
				$worksheet->setCellValueByColumnAndRow(6, $rowIndex, $val->count . $val->unit); //数量+单位
				$worksheet->setCellValueByColumnAndRow(7, $rowIndex, $val->item_names);         //检验项目
				// $worksheet->setCellValueByColumnAndRow(56, $rowIndex, $val->items); //检验项目 --假的
				$worksheet->setCellValueByColumnAndRow(13, $rowIndex, $val->std_num);           //检验标准

				//设置行高
				$worksheet->getRowDimension($rowIndex)->setRowHeight(25);
				$strlen = strlen($val->item_names);
				if ($strlen > 120) {
					$height = floor($strlen / 66) * 20;  //按照每行66个文字计算,计算出行高
					$worksheet->getRowDimension($rowIndex)->setRowHeight($height);
				}
				// break;
				$first_flag = true;
				$rowIndex++;
				$totalCount += $val->count;
			}

			$cell = 'C' . ($rowIndex + 3);
			$cellValue = '共计：' . $totalCount . '件；其他备注：' . $data_contract->remark;
			$worksheet->getCell($cell)->setValue($cellValue); //备注
			// $worksheet->getProtection()->setSheet(true);
			// $worksheet->getProtection()->setPassword('123');
			//样品信息
			$this->exportSampleInfo($spreadsheet, $data_contract, $setting);


			//----------直接在浏览器下载----start
			$file_name = "检验合同" . $data_contract->batchNum . ".xls";                                                        //输出的文件名称
			// header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出Excel 07文件
			header('Content-Type: application/vnd.ms-excel');                                                               //告诉浏览器输出Excel 03版本文件
			header('Content-Disposition: attachment;filename=' . urlencode($file_name));                                    //告诉浏览器输出的文件名称
			header('Cache-Control: max-age=0');                                                                             //禁止缓存
			//----------直接在浏览器下载----end

			$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
			$writer->setPreCalculateFormulas(false); //禁用公式预先计算
			// $writer->save('write.xls');//将Excel文件保存在服务器的磁盘上
			$writer->save('php://output');           //直接在浏览器下载

			/* //未测试通过
			// $writer = new \PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf($spreadsheet);
			$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Mpdf');
			$writer->setPreCalculateFormulas(false);//禁用公式预先计算
			$writer->save("05featuredemo.pdf"); */

			//为了防止内存泄露，建议用完手动清理
			$spreadsheet->disconnectWorksheets();
			unset($spreadsheet);
			unlink($qr_code_path);//删除二维码图片  节省硬盘空间
		}

		/* 样品信息 */
		private function exportSampleInfo($spreadsheet, $data_contract, $setting)
		{
			$template_file           = './storage/ExcelTemplate/检测委托单附表.xls'; //考虑到兼容Linux，斜杠用“/”
			$exist_sample_info_excel = file_exists($template_file);
			if ($exist_sample_info_excel) {
				$spreadsheet_sam_info = IOFactory::load($template_file);    //读取模板文件
				$worksheet_sam_info   = $spreadsheet_sam_info->getSheet(0); //按索引获取表单，从 0 开始
				$worksheet_sam_info->setTitle('样品信息');                      //重命名工作表
				$spreadsheet->addExternalSheet($worksheet_sam_info);        //添加外部工作表
				//为了防止内存泄漏，建议用完手动清理
				$spreadsheet_sam_info->disconnectWorksheets();
				unset($spreadsheet_sam_info);

				$worksheet = $spreadsheet->getSheetByName('样品信息'); //按表单名 获取到激活的表单

				$footerContent = "&C&10第 &P 页，共 &N 页";                        //眉头--右
				$worksheet->getHeaderFooter()->setOddFooter($footerContent);  //页脚  Odd
				$worksheet->getHeaderFooter()->setEvenFooter($footerContent); //页脚  Even
//				$worksheet->getPageMargins()->setHeader(0.5);                 //设置眉头的页边距
				$worksheet->getPageMargins()->setBottom(0.5);                 //设置下边的页边距

				$data_sample_info = DB::table('a_sample_info as B')->select('B.*', 'A.tool_name', 'A.voltage', 'A.class')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $data_contract->id)->orderBy('bar_code')->get();
				$worksheet_sam_info->getCell("A1")->setValue($setting->name_rep);       //合同名称
				$worksheet_sam_info->getCell("J3")->setValue($data_contract->batchNum); //合同编号
				$rowIndex_sam_info = 5;                                                 //样品信息的行号
				foreach ($data_sample_info as $kk => $vv) {
					if ($kk > 0) {
						$worksheet->insertNewRowBefore($rowIndex_sam_info, 1); //在第x行之前插入1个新行
					}
					//				$worksheet_sam_info->getCell("A" . $rowIndex_sam_info)->setValue($kk + 1); //序号
					$worksheet_sam_info->getCell("A" . $rowIndex_sam_info)->setValue($vv->tool_name);                                //样品信息---样品名称
					$worksheet_sam_info->getCell("B" . $rowIndex_sam_info)->setValue(substr($vv->bar_code, -3, 3) . '#');            //样品信息---样品号
					$worksheet_sam_info->getCell("C" . $rowIndex_sam_info)->setValueExplicit($vv->bar_code, DataType::TYPE_STRING2); //样品信息---样品编号
					$worksheet_sam_info->getCell("D" . $rowIndex_sam_info)->setValue($vv->self_num);                                 //样品信息---样品自编号
					$worksheet_sam_info->getCell("E" . $rowIndex_sam_info)->setValue($vv->guige_xinghao);                            //样品信息---规格型号
					$worksheet_sam_info->getCell("F" . $rowIndex_sam_info)->setValue($vv->voltage);                                  //样品信息---额定电压
					$worksheet_sam_info->getCell("G" . $rowIndex_sam_info)->setValue($vv->sample_status);                            //样品信息---样品状态
					$worksheet_sam_info->getCell("H" . $rowIndex_sam_info)->setValue($vv->brand);                                    //样品信息---商标
					$worksheet_sam_info->getCell("I" . $rowIndex_sam_info)->setValue($vv->manufacturer);                             //样品信息---生产厂家
					$worksheet_sam_info->getCell("J" . $rowIndex_sam_info)->setValue($vv->production_date);                          //样品信息---生产日期

					$rowIndex_sam_info++;
				}
				$worksheet_sam_info->getCell("B" . $rowIndex_sam_info)->setValue($data_contract->remark); //备注
			}
		}
	}
